import csv
import datetime
import xlwt

from django.shortcuts import render,redirect,reverse
from django.http import HttpResponse,JsonResponse
from django.db import connection
from django.views.generic import View
from .form import Auth,AuthModelForm
from .models import Auth as AuthModel
from django.template import loader
from django.views.decorators.http import require_http_methods,require_GET,require_POST,require_safe

from .models import Book,Article

# Create your views here.
# 视图

'''
视图一般都写在app的views.py中。并且视图的第一个参数永远都是request（一个HttpRequest）对象。
这个对象存储了请求过来的所有信息，包括携带的参数以及一些头部信息等。
在视图中，一般是完成逻辑相关的操作。比如这个请求是添加一篇博客，
那么可以通过request来接收到这些数据，然后存储到数据库中，
最后再把执行的结果返回给浏览器。视图函数的返回结果必须是HttpResponseBase对象或者子类的对象。
示例代码如下：
'''
def book_list(request):
    return HttpResponse("书籍列表！")

# URL中添加参数：
# http://127.0.0.1:8000/book/1/
# def book_detail(request,book_id):
#     text = "您输入的书籍的id是: %s" %book_id
#     return HttpResponse(text)
# http://127.0.0.1:8000/book/get_many/?name=lin&age=23
def getMany(request):
    name = request.GET.get('name','')
    age = request.GET.get('age',0)
    return HttpResponse('hello i am {0},age is {1}'.format(name,age))

# http://127.0.0.1:8000/book/get_url/name/30
def getUrl(request,name,age):
    return HttpResponse('hello i am {0},age is {1}'.format(name,age))

# http://127.0.0.1:8000/getId/?id=4
def get_id(request):
    book_id =request.GET.get("id")
    text = "您输入的书籍的id是: %s" % book_id
    return HttpResponse(text)

# 面向对象
# http://127.0.0.1:8000/book/class_index/name/30
class getIdex(View):
    def get(self,request,name,age):
        return HttpResponse('hello i am {0},age is {1}'.format(name,age))


# 模板练习
class temIdex(View):
    TEMPLATE = 'tem_index.html'
    def get(self,request,name):
        data = {}
        data['name'] = name
        data['array'] = range(10)
        data['count'] = 20
        data['time'] = datetime.datetime.now()
        data['cut_str'] = 'hello-boy!'
        data['first_big'] = '你好 django！haha'
        data['float_num'] = 3.1464625
        data['html_str'] = '<div style="background-color:red;width:50px;height:50px"></div>'
        data['a_str'] = '请看 www.baidu.com'
        data['reature'] = data['time']+datetime.timedelta(days=5)
        return render(request,self.TEMPLATE,data)


# 表单联系
class Regist(View):
    TEMPLATE = 'regist.html'

    def get(self,request):
        # 查询
        user = AuthModel.objects.filter(pk=1).first()
        if user:
            form = AuthModelForm(instance=user)
        else:
            form = AuthModelForm()
        # form = Auth()
        return render(request,self.TEMPLATE,{'form':form})

    def post(self,request):
        # 获取 前端的传值
        # username = request.POST.get("username")
        # passworld = request.POST.get("password")
        # form = Auth(request.POST)
        form = AuthModelForm(request.POST)
        if form.is_valid():
            username = form.cleaned_data.get('username')
            password = form.cleaned_data.get('password')
            # 保存
            form.save()
            print('username',username)
            print('password',password)
        else:
            return render(request,self.TEMPLATE,{'form':form})

        return redirect('/book/regist/')

'''
 在Django中操作数据库：
 在Django中操作数据库有两种方式。
 第一种方式就是使用原生sql语句操作，
 第二种就是使用ORM模型来操作
'''

#使用原生sql语句操作，
def index(request):
    cursor = connection.cursor()
    # 插入
    # cursor.execute("insert into db_book(id,name,author) values(null,'三国演义','罗贯中')")
    cursor.execute("select id,name,author from db_book")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    return render(request,'index.html')

def get_corsor():
    return connection.cursor()

def sqIndex(request):
    cursor = get_corsor()
    cursor.execute("select id,name,author from db_book")
    books = cursor.fetchall()
    return render(request,'index.html',context={"books":books})


def add_book(request):
    if request.method == 'GET':
        return render(request,'add_book.html')
    else:
        name = request.POST.get("name")
        author = request.POST.get("author")
        cursor = get_corsor()
        cursor.execute("insert into db_book(id,name,author) values(null,'%s','%s')" % (name,author))
        return redirect(reverse('index'))


def book_detail(request,book_id):
    cursor = get_corsor()
    cursor.execute("select id,name,author from db_book where id=%s" % book_id)
    book = cursor.fetchone()
    return render(request,'book_detail.html',context={"book":book})

def delete_book(request):
    if request.method == 'POST':
        book_id = request.POST.get('book_id')
        cursor = get_corsor()
        cursor.execute("delete from book where id=%s" %book_id)
        return redirect(reverse('index'))
    else:
        raise RuntimeError("删除图书的method错误!")


# orm 基本操作
def ormIdex(request):
    # 1.使用orm添加一条数据到数据库中
    #book = Book(name="七龙珠",auhor="鸟山明",price=100)
    #book.save()

    # 2.查询
    # 2.1 根据主键进行查找
    # primary key
    # get 找不到数据会报错
    # book = Book.objects.get(pk=1)
    # print(book)
    # 2.2 根据其他条件进行查找
    # books =Book.objects.filter(name="七龙珠").first()
    # print(books)

    # 删除数据
    # book = Book.objects.get(pk=2)
    # book.delete()

    # 修改数据
    book = Book.objects.get(pk=1)
    book.price = 200
    book.save()
    return HttpResponse("orm基本操作")


# 高级视图
# Django限制请求method
@require_GET
def methon_index(request):
    # 首页返回所有的文章
    # 只能使用GET请求来访问这个视图函数
    acticles = Article.objects.all()
    print(acticles)
    return render(request,'acticles.html',context={"acticles":acticles})

@require_http_methods(['POST','GET'])
def add_article(request):
    # 如果使用GET请求来访问这个视图函数，那么就返回一个添加文章的HTML页面
    # 如果使用POST请求来访问这个视图函数，那么就获取提交上来的数据，然后保存
    # 到数据库中
    if request.method == 'GET':
        return render(request,'add_article.html')
    else:
        title = request.POST.get("title")
        content = request.POST.get("content")
        Article.objects.create(title=title,content=content)
        return HttpResponse("success")

# 返回json格式给前端
#用来对象dump成json字符串，然后返回将json字符串封装成Response对象返回给浏览器。并且他的Content-Type是application/json
# 默认情况下JsonResponse只能对字典进行dump，如果想要对非字典的数据进行dump，那么需要给JsonResponse传递一个safe=False参数
def jsonresponse_view(request):
    persons = [
        {
            'username':'zhiliao',
            'age':18,
            'height':180,
        },
        {
            'username': 'zhiliao1',
            'age': 20,
            'height': 180
        }
    ]
    response = JsonResponse(persons,safe=False)
    return response


# 生成csv文件
def csv_view(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = "attachment;filename='abc.csv'"
    # with open('xx.csv','w') as fp:
    #     csv.writer(fp)
    writer = csv.writer(response)
    writer.writerow(['username', 'age'])
    writer.writerow(['zhiliao', 18])
    return response

def template_csv_view(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = "attachment;filename='abc.csv'"
    context = {
        'rows': [
            ['username','age'],
            ['zhiliao',18],
        ]
    }
    template = loader.get_template('abc.txt')
    csv_template = template.render(context)
    response.content = csv_template
    return response
# 导出数据到CSV文件
def export_users_csv(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="users.csv"'
    writer = csv.writer(response)
    writer.writerow(['Username', 'First name', 'Last name', 'Email address'])
    # QuerySet中使用values_list
    #users = User.objects.all().values_list('username', 'first_name', 'last_name', 'email')
    #for user in users:
    writer.writerow(['Username1','First1','Last name1','Email address1'])
    writer.writerow(['Username2', 'First1', 'Last name1', 'Email address1'])
    writer.writerow(['Username3', 'First1', 'Last name1', 'Email address1'])
    return response


# 将数据导出到XLS文件
#如果您确实需要导出到.xls文件，请使用这个方法。你将能够添加格式为粗体字体、字体大小、定义列大小等。

#首先，安装xlwt模块。最简单的方法是使用pip
# pip install xlwt
#  can't subtract offset-naive and offset-aware datetimes
def export_users_xls(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="users.xls"'
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('Users')
    row_num = 0
    font_style = xlwt.XFStyle()
    font_style.font.bold = True
    columns = ['序号', '标题', '内容', '价钱', ]
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)

    font_style = xlwt.XFStyle()

    rows = Article.objects.all().values_list('id','title','content', 'price')
    for row in rows:
        row_num += 1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
    wb.save(response)

    return response


# 类视图
# 所有的类视图都是继承自他。如果我们写自己的类视图，也可以继承自他。然后再根据当前请求的method，来实现不同的方法
#比如这个视图只能使用get的方式来请求，那么就可以在这个类中定义get(self,request,*args,**kwargs)方法。以此类推，
# 如果只需要实现post方法，那么就只需要在类中实现post(self,request,*args,**kwargs)
class BookListView(View):
    def get(self, request, *args, **kwargs):
        return HttpResponse("book list view")


class AddBookView(View):
    def get(self, request, *args, **kwargs):
        return render(request, 'add_book.html')

    def post(self, request, *args, **kwargs):
        book_name = request.POST.get("name")
        book_author = request.POST.get("author")
        print("name:{},author:{}".format(book_name, book_author))
        return HttpResponse("success")


class BookDetailView(View):
    def get(self, request, book_id):
        print('图书的id是：%s' % book_id)
        return HttpResponse("success")

    def dispatch(self, request, *args, **kwargs):
        print('dispatch')
        return super(BookDetailView, self).dispatch(request, *args, **kwargs)

    def http_method_not_allowed(self, request, *args, **kwargs):
        return HttpResponse("不支持GET以外的其他请求！")







